﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook
{
    public class CustomerInfoManager:ConnectionManager
    {
        #region Adding Card
        public SqlException AddNewCustomer(CustomerInfoUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.Text;
                oCommand.CommandText = "INSERT INTO M_CUSTOMERINFO (CardCode, CardName, BPType, UnitNo, Street, Brgy, CityOrMunicipal, PostalCode, ProvinceOrMetro, Phone, Fax, ContactPerson, TIN) VALUES (@CardCode, @CardName, @BPType, @UnitNo, @Street, @Brgy, @CityOrMunicipal, @PostalCode, @ProvinceOrMetro, @Phone, @Fax, @ContactPerson, @TIN)";

                oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@BPType", oUnit.Type));
                oCommand.Parameters.Add(new SqlParameter("@UnitNo", oUnit.UnitNo));
                oCommand.Parameters.Add(new SqlParameter("@Street", oUnit.Street));
                oCommand.Parameters.Add(new SqlParameter("@Brgy", oUnit.Barangay));
                oCommand.Parameters.Add(new SqlParameter("@CityOrMunicipal", oUnit.CityOrMunicipality));
                oCommand.Parameters.Add(new SqlParameter("@PostalCode", oUnit.PostalCode));
                oCommand.Parameters.Add(new SqlParameter("@ProvinceOrMetro", oUnit.ProvinceOrMetro));
                oCommand.Parameters.Add(new SqlParameter("@Phone", oUnit.Phone));
                oCommand.Parameters.Add(new SqlParameter("@Fax", oUnit.Fax));
                oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
                oCommand.Parameters.Add(new SqlParameter("@TIN", oUnit.TIN));

                oCommand.ExecuteNonQuery();

                return null;

            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();   
            }
        }

        public SqlException UpdateCustomerInfo(CustomerInfoUnit oUnit)
        {
            SqlCommand _COMMAND = new SqlCommand();
            try
            {


                _COMMAND.Connection = this.Connection;
                _COMMAND.CommandType = CommandType.Text;

                _COMMAND.CommandText = "UPDATE M_CUSTOMERINFO " +
                                        "SET CardName=@CardName, " +
                                            "BPType=@BPType,  " +
                                            "UnitNo=@UnitNo, " +
                                            "Street=@Street, " +
                                            "Brgy=@Brgy, " +
                                            "CityOrMunicipal=@CityOrMunicipal, " +
                                            "PostalCode=@PostalCode, " +
                                            "ProvinceOrMetro=@ProvinceOrMetro, " +
                                            "Phone=@Phone, " +
                                            "Fax=@Fax, " +
                                            "ContactPerson=@ContactPerson, " +
                                            "TIN=@TIN " +
                                         "WHERE CardCode = @CardCode";

                _COMMAND.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
                _COMMAND.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
                _COMMAND.Parameters.Add(new SqlParameter("@BPType", oUnit.Type));

                _COMMAND.Parameters.Add(new SqlParameter("@UnitNo", oUnit.UnitNo));
                _COMMAND.Parameters.Add(new SqlParameter("@Street", oUnit.Street));
                _COMMAND.Parameters.Add(new SqlParameter("@Brgy", oUnit.Barangay));
                _COMMAND.Parameters.Add(new SqlParameter("@CityOrMunicipal", oUnit.CityOrMunicipality));
                _COMMAND.Parameters.Add(new SqlParameter("@PostalCode", oUnit.PostalCode));
                _COMMAND.Parameters.Add(new SqlParameter("@ProvinceOrMetro", oUnit.ProvinceOrMetro));

                _COMMAND.Parameters.Add(new SqlParameter("@Phone", oUnit.Phone));
                _COMMAND.Parameters.Add(new SqlParameter("@Fax", oUnit.Fax));
                _COMMAND.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));

                _COMMAND.Parameters.Add(new SqlParameter("@TIN", oUnit.TIN));

                _COMMAND.ExecuteNonQuery();

                return null;

            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                _COMMAND.Dispose();
            }
        }
        #endregion

        #region Get Records
        public DataTable GetAllPartnersByBPType(string sBPType)
        {
            try
            {
                SqlCommand _COMMAND = new SqlCommand();
                SqlDataAdapter _ADAPTER = new SqlDataAdapter();
                DataSet ds = new DataSet();

                _COMMAND.Connection = this.Connection;
                _COMMAND.CommandType = CommandType.Text;
                _COMMAND.CommandText = "SELECT CardCode, CardName FROM M_CUSTOMERINFO WHERE BPType=@BPType";

                _COMMAND.Parameters.Add(new SqlParameter("@BPType", sBPType));

                _ADAPTER.SelectCommand = _COMMAND;

                _ADAPTER.Fill(ds);

                return ds.Tables[0];


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable BrowseByCardCode(string sCode, string sType)
        {
            try
            {
                SqlCommand _COMMAND = new SqlCommand();
                SqlDataAdapter _ADAPTER = new SqlDataAdapter();
                DataSet ds = new DataSet();

                _COMMAND.Connection = this.Connection;
                _COMMAND.CommandType = CommandType.Text;
                _COMMAND.CommandText = "SELECT TOP 300 M_CUSTOMERINFO.* FROM M_CUSTOMERINFO WHERE BPType=@BPType AND CardCode LIKE @CardCode ORDER BY CardCode";
                _COMMAND.Parameters.Add(new SqlParameter("@CardCode", sCode));
                _COMMAND.Parameters.Add(new SqlParameter("@BPType", sType));

                _ADAPTER.SelectCommand = _COMMAND;

                _ADAPTER.Fill(ds);

                return ds.Tables[0];


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable BrowseByCardName(string sName, string sType)
        {
            try
            {
                SqlCommand _COMMAND = new SqlCommand();
                SqlDataAdapter _ADAPTER = new SqlDataAdapter();
                DataSet ds = new DataSet();

                _COMMAND.Connection = this.Connection;
                _COMMAND.CommandType = CommandType.Text;
                _COMMAND.CommandText = "SELECT TOP 300 M_CUSTOMERINFO.* FROM M_CUSTOMERINFO WHERE BPType=@BPType AND CardName LIKE @CardName ORDER BY CardName";
                _COMMAND.Parameters.Add(new SqlParameter("@CardName", sName));
                _COMMAND.Parameters.Add(new SqlParameter("@BPType", sType));

                _ADAPTER.SelectCommand = _COMMAND;

                _ADAPTER.Fill(ds);

                return ds.Tables[0];


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable BrowseByStoreCode(string _STORECODE, string sType)
        {
            try
            {
                SqlCommand _COMMAND = new SqlCommand();
                SqlDataAdapter _ADAPTER = new SqlDataAdapter();
                DataSet ds = new DataSet();

                _COMMAND.Connection = this.Connection;
                _COMMAND.CommandType = CommandType.Text;
                _COMMAND.CommandText = "SELECT TOP 300 M_CUSTOMERINFO.* FROM M_CUSTOMERINFO WHERE BPType=@BPType AND StoreCode LIKE @StoreCode ORDER BY StoreCode";
                _COMMAND.Parameters.Add(new SqlParameter("@StoreCode", _STORECODE));
                _COMMAND.Parameters.Add(new SqlParameter("@BPType", sType));

                _ADAPTER.SelectCommand = _COMMAND;

                _ADAPTER.Fill(ds);

                return ds.Tables[0];


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetByStoreCode(string _CODE, string _TYPE)
        {
            try
            {
                SqlCommand _COMMAND = new SqlCommand();
                SqlDataAdapter _ADAPTER = new SqlDataAdapter();
                DataSet ds = new DataSet();

                _COMMAND.Connection = this.Connection;
                _COMMAND.CommandType = CommandType.Text;
                _COMMAND.CommandText = "SELECT M_CUSTOMERINFO.* FROM M_CUSTOMERINFO  WHERE BPType=@BPType AND StoreCode=@StoreCode";
                _COMMAND.Parameters.Add(new SqlParameter("@StoreCode", _CODE));
                _COMMAND.Parameters.Add(new SqlParameter("@BPType", _TYPE));

                _ADAPTER.SelectCommand = _COMMAND;

                _ADAPTER.Fill(ds);

                return ds.Tables[0];


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetByCardCode(string _CODE, string _TYPE)
        {
            try
            {
                SqlCommand _COMMAND = new SqlCommand();
                SqlDataAdapter _ADAPTER = new SqlDataAdapter();
                DataSet ds = new DataSet();

                _COMMAND.Connection = this.Connection;
                _COMMAND.CommandType = CommandType.Text;
                _COMMAND.CommandText = "SELECT M_CUSTOMERINFO.* FROM M_CUSTOMERINFO  WHERE BPType=@BPType AND CardCode=@CardCode";
                _COMMAND.Parameters.Add(new SqlParameter("@CardCode", _CODE));
                _COMMAND.Parameters.Add(new SqlParameter("@BPType", _TYPE));

                _ADAPTER.SelectCommand = _COMMAND;

                _ADAPTER.Fill(ds);

                return ds.Tables[0];


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

    }
}
